package com.dudu.util;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class Test {
    public static void main(String[] args) throws IOException {
        // 创建新的excel
        XSSFWorkbook wbCreat = new XSSFWorkbook();
        // 打开已有的excel
        String strExcelPath = "/Users/bjhl/Desktop/template.xlsx";
        InputStream in = new FileInputStream(strExcelPath);
        XSSFWorkbook wb = new XSSFWorkbook(in);

        for (int ii = 0; ii < wb.getNumberOfSheets(); ii++) {
            XSSFSheet sheet = wb.getSheetAt(ii);
            XSSFSheet sheetCreat = wbCreat.createSheet(sheet.getSheetName());
            // 复制源表中的合并单元格
            MergerRegion(sheetCreat, sheet);

            int firstRow = sheet.getFirstRowNum();
            int lastRow = sheet.getLastRowNum();

            for (int i = firstRow; i <= lastRow; i++) {
                // 创建新建excel Sheet的行
                XSSFRow rowCreat = sheetCreat.createRow(i);
                // 取得源有excel Sheet的行
                XSSFRow row = sheet.getRow(i);
                // 单元格式样
                int firstCell = row.getFirstCellNum();
                int lastCell = row.getLastCellNum();

                for (int j = firstCell; j < lastCell; j++) {
                    // 自动适应列宽 貌似不起作用
                    System.out.println(row.getCell(j));
                    rowCreat.createCell(j);

                    String strVal = "";

                    if (row.getCell(j) == null) {
                    } else {
                        strVal = removeInternalBlank(row.getCell(j)
                                .getStringCellValue());
                    }

                    rowCreat.getCell(j).setCellValue(strVal);
                }
            }
        }

        FileOutputStream fileOut = new FileOutputStream("/Users/bjhl/Desktop/template2.xlsx");
        wbCreat.write(fileOut);
        fileOut.close();
    }

    /**
     * 复制原有sheet的合并单元格到新创建的sheet
     * 
     * @param sheetCreat 新创建sheet
     * @param sheet 原有的sheet
     */
    private static void MergerRegion(XSSFSheet sheetCreat, XSSFSheet sheet) {
        int sheetMergerCount = sheet.getNumMergedRegions();

        for (int i = 0; i < sheetMergerCount; i++) {
            CellRangeAddress mergedRegionAt = sheet.getMergedRegion(i);
            sheetCreat.addMergedRegion(mergedRegionAt);
        }
    }

    /**
     * 去除字符串内部空格
     */
    public static String removeInternalBlank(String s) {
        Pattern p = Pattern.compile("\\s*|\t|\r|\n");
        Matcher m = p.matcher(s);
        char[] str = s.toCharArray();
        StringBuffer sb = new StringBuffer();

        for (int i = 0; i < str.length; i++) {
            if (str[i] == ' ') {
                sb.append(' ');
            } else {
                break;
            }
        }

        String after = m.replaceAll("");

        return sb.toString() + after;
    }
}

